﻿/********************************************************************************************************
  1. 스토어드명		: SP_STANDARDCODE_CUD
  2. 목적 & 기능		: Standard Code 추가/수정/삭제
  3. 생성일자		: 2014.07.07
  4. 생성자 			: 조범준
  5. Used DB Name	: DB_EDUC
  6. Used DB Table	:
						CREATE	- STDCODE a
						UPDATE	- STDCODE a
  7. 실행			:
********************************************************************************************************/
----------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_STANDARDCODE_CUD
(
	iJOBMODE			IN VARCHAR2,
	iOBJECTID			IN NUMBER,
	iPAR_OBJECTID		IN NUMBER,
	iGRP_OBJECTID		IN NUMBER,
	iCODE				IN VARCHAR2,
	iPATTERN			IN VARCHAR2,
	iKOR_NAME			IN VARCHAR2,
	iCHARACTERISTIC		IN VARCHAR2,
	iSEQ				IN NUMBER,
	iCODE_DEFAULT		IN NUMBER,
	iSTATE				IN NUMBER
)
IS
BEGIN
----------------------------------------------------------------------------------------------------------
-- 작업구분
----------------------------------------------------------------------------------------------------------
    IF iJOBMODE = 'CREATE_CODE' THEN
        GOTO CREATE_CODE;
    ELSIF iJOBMODE = 'UPDATE_CODE' THEN
        GOTO UPDATE_CODE;
    END IF;
----------------------------------------------------------------------------------------------------------
-- Standarad Code 추가
----------------------------------------------------------------------------------------------------------
<<CREATE_CODE>>
	INSERT INTO	STDCODE (OBJECTID, PAR_OBJECTID, GRP_OBJECTID, CODE, PATTERN, KOR_NAME, CHARACTERISTIC, SEQ, CODE_DEFAULT, STATE, CREATE_DATE)
	VALUES (SEQ_STDCODE.NEXTVAL, iPAR_OBJECTID, iGRP_OBJECTID, iCODE, iPATTERN, iKOR_NAME, iCHARACTERISTIC, iSEQ, iCODE_DEFAULT, 0, SYSDATE);
	RETURN;
----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------
-- Standarad Code 수정
----------------------------------------------------------------------------------------------------------
<<UPDATE_CODE>>
	UPDATE	STDCODE A
	SET		A.PAR_OBJECTID		=	iPAR_OBJECTID,
			A.GRP_OBJECTID		=	iGRP_OBJECTID,
			A.CODE				=	iCODE,
			A.PATTERN			=	iPATTERN,
			A.KOR_NAME			=	iKOR_NAME,
			A.CHARACTERISTIC	=	iCHARACTERISTIC,
			A.SEQ				=	iSEQ,
			A.CODE_DEFAULT		=	iCODE_DEFAULT,
			A.STATE				=	iSTATE,
			A.MODIFY_DATE		=	SYSDATE
	WHERE A.OBJECTID		= iOBJECTID;
	RETURN;
----------------------------------------------------------------------------------------------------------
END SP_STANDARDCODE_CUD;
